package com.newgrand.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.ValueFilter;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.newgrand.mapper.ComSqlMapper;
import com.newgrand.mapper.UipQueryMapper;
import com.newgrand.model.DetailDataModel;
import com.newgrand.model.QueryModel;
import com.newgrand.model.QueryResModel;
import com.newgrand.model.UipQuery;
import com.newgrand.service.QueryService;
import com.newgrand.utils.i8util.QueryResultUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author ZhaoFengjie
 * @version 1.0
 * @date 2022/9/7 13:24
 */
@Service
public class QueryServiceImpl implements QueryService {

    @Autowired
    private ComSqlMapper comSqlMapper;

    @Autowired
    private UipQueryMapper uipQueryMapper;


    @Override
    public QueryResModel queryData(String type, QueryModel queryModel) {

        LambdaQueryWrapper<UipQuery> mst = new LambdaQueryWrapper<>();
        mst.eq(UipQuery::getBill_no, type);
        UipQuery main = uipQueryMapper.selectOne(mst);
        if (main == null) {
            return QueryResultUtil.error();
        }
        //得到sql查询语句
        String sql = main.getSqlstr();
        //得到主表名称
        String mainTable = main.getTablename();
        //过滤条件
        String params = queryModel.getFilter();

//        //得到附件表名
//        String fileName = main.getFilename();
        //得到单点地址
//        String ssoUrl = StringUtils.isEmpty(main.getSsourl()) ? "" : main.getSsourl();
        if (!StringUtils.isEmpty(params)) {
            Pattern p = Pattern.compile("(?<!\\w)(?i)(INSERT|DELETE|TRUNCATE|UPDATE|CREATE|ALTER|DROP)(?!\\w)", Pattern.CASE_INSENSITIVE);
            Matcher m = p.matcher(params);
            if (m.find()) {
                return QueryResultUtil.error("SQL语句只支持select语句！");
            }
            sql = "select * from (" + sql + ") a where " + params;
        }
        //查询总条数
        String totalNum = comSqlMapper.queryString("select count(*) from (" + sql + ")");
        if ("0".equals(totalNum)) {
            return QueryResultUtil.success("查询结果为空！");
        }

        Integer total = Integer.parseInt(totalNum);
        //页码
        Integer page = Integer.parseInt(queryModel.getPage());
        //页码条数
        Integer pageSize = Integer.parseInt(queryModel.getPageSize());
        //总页数 （总条数 / 页码条数 并向上取整）
        Integer pages = (total + pageSize - 1) / pageSize;
        //页数大于总页数返回空数组
        if (page > pages) {
            return QueryResultUtil.success(page, pageSize, total, pages, "查询成功", new ArrayList<>());
        }

        //查询主表数据语句
        String sqlQuery = getSQL(sql, page, pageSize);
        //主表信息
        List<Map<String, Object>> mainData = comSqlMapper.queryObject(sqlQuery);

        LambdaQueryWrapper<UipQuery> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(UipQuery::getPphid, main.getPhid());
        List<UipQuery> detail = uipQueryMapper.selectList(queryWrapper);
        if (detail.size() > 0) {
            //明细表sql
            List<DetailDataModel> detailData = new ArrayList<>();
            Integer liend = 0;
            String lineName = "itemList";
            for (UipQuery item : detail) {
                //主表phids
                List<String> mainPhIds = mainData.stream().map(t -> t.get("phid").toString()).collect(Collectors.toList());
                String detailSQL = item.getSqlstr() + " where pphid in ('" + String.join("','", mainPhIds) + "') ";
                String asName = item.getAsname();
                List<Map<String, Object>> data = comSqlMapper.queryObject(detailSQL);
                if (StringUtils.isEmpty(asName)) {
                    if (liend > 0) {
                        lineName += liend;
                        liend++;
                    }
                } else {
                    lineName = asName;
                }
                //获取明细表主键
                List<String> dphids = data.stream().map(t -> t.get("phid").toString()).collect(Collectors.toList());
                //明细附件
                //List<AsrQueryModel> dinfos = getInfo(item.getU_filename(), item.getU_tablename(), dphids);
                DetailDataModel itemData = new DetailDataModel();
                itemData.setName(lineName);
                itemData.setData(data);
                itemData.setFilesInfo(new ArrayList<>());
                itemData.setTableName(item.getTablename());
                itemData.setFileName("");
                detailData.add(itemData);
            }
            for (Map<String, Object> item : mainData) {
                //主表主键
                String mainPhId = item.get("phid").toString();
                for (DetailDataModel dItem : detailData) {
                    List<Map<String, Object>> nameData = dItem.getData().stream().filter(t -> mainPhId.equals(t.get("pphid").toString())).collect(Collectors.toList());
                    item.put(dItem.getName(), nameData);
                }
            }
        }
        return QueryResultUtil.success(page, pageSize, total, pages, "调用成功", JSONObject.parseArray(JSONObject.toJSONString(mainData, filter)));
    }

    /**
     * 获取分页取数sql
     *
     * @param sql
     * @param page
     * @param pageSize
     * @return
     */
    private String getSQL(String sql, Integer page, Integer pageSize) {
        Integer beginIndex = 0;
        Integer endIndex = pageSize;
        if (page > 1) {
            page = page - 1;
            beginIndex = page * pageSize + 1;
            endIndex = beginIndex + pageSize - 1;
        }
        return " select * from (select uip.*, ROW_NUMBER() OVER(Order by uip.phid) ROW_NUMBER from (" + sql + ") uip ) uipb \n" +
                " where ROW_NUMBER BETWEEN " + beginIndex + " AND " + endIndex;
    }

    private static ValueFilter filter = (obj, s, v) -> {
        if (v == null)
            return "";
        return v;
    };
}
